package ncs.common.database;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import ncs.common.model.Resource;
import ncs.common.util.ConnectDB;

/**
 * @author Jian
 *+-------------+--------------+------+-----+-------------------+----------------+
| Field       | Type         | Null | Key | Default           | Extra          |
+-------------+--------------+------+-----+-------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL              | auto_increment |
| course_id   | int(11)      | NO   | MUL | NULL              |                |
| name        | varchar(256) | YES  |     | NULL              |                |
| size        | int(11)      | YES  |     | 0                 |                |
| download    | int(11)      | YES  |     | 0                 |                |
| upload_time | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| uploader    | varchar(50)  | YES  |     | NULL              |                |
| localname   | varchar(256) | YES  |     | NULL    		  |                |
+-------------+--------------+------+-----+-------------------+----------------+
 */
public class ResourceDAO {
	public void addResource(Resource r)throws Exception
	{
		String sql="insert into tb_resource values(?,?,?,?,?,?,?,?)";
		Connection conn=ConnectDB.getConnection();
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setInt(1, r.getId());
		ps.setInt(2, r.getCourse_id());
		ps.setString(3, r.getName());
		ps.setInt(4, r.getSize());
		ps.setInt(5, r.getDownload());
		ps.setDate(6,new Date(Calendar.getInstance().getTimeInMillis()));
		ps.setString(7, r.getUploader());
		ps.setString(8, r.getLocalName());
		ps.execute();
		ps.close();
		conn.close();
	}
	public List<Resource>getResource(int start, int limits)throws Exception
	{
		String sql="select * from tb_resource limit ?,?";
		Connection conn=ConnectDB.getConnection();
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setInt(1, start);
		ps.setInt(2, limits);
		ResultSet rs=ps.executeQuery();
		List<Resource>resource_list=new ArrayList<Resource>();
		while(rs.next())
		{
			Resource r=new Resource();
			r.setId(rs.getInt(1));
			r.setCourse_id(rs.getInt(2));
			r.setName(rs.getString(3));
			r.setSize(rs.getInt(4));
			r.setDownload(rs.getInt(5));
			r.setUpload_time(rs.getString(6));
			r.setUploader(rs.getString(7));
			resource_list.add(r);
		}
		rs.close();
		ps.close();
		conn.close();
		return resource_list;
	}
	public  int getResourceCount() throws Exception
	{
		String sql="select count(*) from tb_resource";
		Connection conn=ConnectDB.getConnection();
		Statement st=conn.createStatement();
		ResultSet rs=st.executeQuery(sql);
		rs.next();
		int ret=rs.getInt(1);
		rs.close();
		st.close();
		conn.close();
		return ret;
	}
	public static Resource getLocalName(int id,boolean isdownload) throws Exception
	{
		Resource resource=new Resource();
		String sql="select localname,size,course_id,name from tb_resource where id=?";
		Connection conn=ConnectDB.getConnection();
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet rs=ps.executeQuery();
		if(rs.next()==false)
			return null;
		resource.setLocalName(rs.getString(1));
		resource.setSize(rs.getInt(2));
		resource.setCourse_id(rs.getInt(3));
		resource.setName(rs.getString(4));
		rs.close();
		ps.close();
		if(isdownload)
		{
			sql="update tb_resource set download=download+1 where id=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.execute();
			ps.close();
			
		}
		conn.close();
		return resource;
	}
}
